#!/bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto
MYSQL_HOME=/usr/bin/mysql
HIVE_HOME=/usr/bin/hive

start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dws insert table start ${start_time}" >> ./sqoop_liujie.log

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_dws.dws_stu_attendance_day_count
with tbl_tmp as (
select
       class_id,
       studying_student_count,
       student_id,
       morning_is_leave,
       afternoon_is_leave,
       evening_is_leave,
       morning_is_late,
       afternoon_is_late,
       evening_is_late,
       studying_date,
       row_number() over(partition by class_id,studying_date ) rn
from hive.edu_dwb.dwb_stu_attendance_detail t
),
calc_count as (
    select studying_date,
           class_id,
           max(if(rn = 1, studying_student_count, null)) as student_count,
           -- 正常出勤
           sum(if(morning_is_late = 0, 1, 0))            as attendance_morning_cnt,
           sum(if(afternoon_is_late = 0, 1, 0))          as attendance_afternoon_cnt,
           sum(if(evening_is_late = 0, 1, 0))            as attendance_evening_cnt,
           -- 迟到
           sum(if(morning_is_late = 1, 1, 0))            as late_morning_cnt,
           sum(if(afternoon_is_late = 1, 1, 0))          as late_afternoon_cnt,
           sum(if(evening_is_late = 1, 1, 0))            as late_evening_cnt,
           -- 请假
           sum(if(morning_is_leave = 1, 1, 0))           as leave_morning_cnt,
           sum(if(afternoon_is_leave = 1, 1, 0))         as leave_afternoon_cnt,
           sum(if(evening_is_leave = 1, 1, 0))           as leave_evening_cnt
    from tbl_tmp
    group by studying_date, class_id
),
attendance_count as (
select studying_date,
       class_id,
       student_count,
       coalesce (attendance_morning_cnt,0) as  attendance_morning_cnt,
       coalesce (attendance_afternoon_cnt,0) as attendance_afternoon_cnt,
       coalesce (attendance_evening_cnt,0) as attendance_evening_cnt,

       coalesce (late_morning_cnt,0) as late_morning_cnt,
       coalesce (late_afternoon_cnt,0) as late_afternoon_cnt,
       coalesce (late_evening_cnt,0) as late_evening_cnt,

       coalesce (leave_morning_cnt,0) as leave_morning_cnt,
       coalesce (leave_afternoon_cnt,0) as leave_afternoon_cnt,
       coalesce (leave_evening_cnt,0) as leave_evening_cnt,

       student_count - coalesce (attendance_morning_cnt,0)
           - coalesce (late_morning_cnt,0) - coalesce (leave_morning_cnt,0) as truant_morning_cnt,
       student_count - coalesce (attendance_afternoon_cnt,0)
           - coalesce (late_afternoon_cnt,0) - coalesce (leave_afternoon_cnt,0) as truant_afternoon_cnt,
       student_count - coalesce (attendance_evening_cnt,0)
           - coalesce (late_evening_cnt,0) - coalesce (leave_evening_cnt,0) as truant_evening_cnt

from calc_count c)
select
       class_id,
       student_count,
       attendance_morning_cnt as morning_attendance_cnt,
       leave_morning_cnt as morning_leave_cnt,
       late_morning_cnt as morning_late_cnt,
       truant_morning_cnt as morning_truant_cnt,
       attendance_afternoon_cnt as afternoon_attendance_cnt,
       leave_afternoon_cnt as afternoon_leave_cnt,
       late_afternoon_cnt as afternoon_late_cnt,
       truant_afternoon_cnt as afternoon_truant_cnt,
       attendance_evening_cnt as evening_attendance_cnt,
       leave_evening_cnt as evening_leave_cnt,
       late_evening_cnt as evening_late_cnt,
       truant_evening_cnt as evening_truant_cnt,
       studying_date
from attendance_count
;
"


start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dws insert table end ${start_time}" >> ./sqoop_liujie.log

